﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Ext.Net;
using System.Data;
using System.Text;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

public partial class CRM_Manager_ERP_CargoExcel : BasePage
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            DataTable dt = CRMMis.BLL.ERP_Customer.GetList("1=1").Tables[0];
            Store_Custom.DataSource = dt;
            Store_Custom.DataBind();
        }
    }
    


    ///<summary>
    /// 上传Excel
    ///</summary>
    [DirectMethod]
    public void ImportExcel()
    {
        if (extFuf_ImportExcel.HasFile)
        {
            string strExt = string.Empty;
            string strName = "";
            string strPathName = "";
            string sSize = "";

            if (extFuf_ImportExcel.PostedFile != null && extFuf_ImportExcel.PostedFile.ContentLength > 0)
            {
                strName = extFuf_ImportExcel.PostedFile.FileName;
                sSize = extFuf_ImportExcel.PostedFile.ContentLength.ToString();
                strExt = System.IO.Path.GetExtension(strName);
                strPathName = Guid.NewGuid().ToString() + strExt;
                if (strExt.ToUpper() != ".XLS")
                {
                    X.Msg.Alert("提示", "Excel格式应为.xls", MessageBox.Icon.WARNING).Show();
                    return;
                }
            }

            string strMapPath = Server.MapPath("../TmpFile/");
            string strPath = strMapPath + strPathName;
            extFuf_ImportExcel.PostedFile.SaveAs(strPath);
            OpenExcel(strPath, strExt);
    }
        else
        {
            X.Msg.Alert("提示", "请选择Excel", MessageBox.Icon.NONE).Show();
}
    }
    

    public DataTable SessionKehuExcel
    {
        get { return Session["SessionKehuExcel"] as DataTable; }
        set { Session["SessionKehuExcel"] = value; }
    }

    private void OpenExcel(string filePath,string strExt)
    {
        System.IO.FileStream stream = new System.IO.FileStream(filePath, System.IO.FileMode.Open, System.IO.FileAccess.Read);//读入excel模板
        IWorkbook Iworkbook = null;
        if (strExt.ToUpper() == ".XLSX") // 2007版本
            Iworkbook = new XSSFWorkbook(stream);
        else if (strExt.ToUpper() == ".XLS") // 2003版本
            Iworkbook = new HSSFWorkbook(stream);

        //处理模板数据
        ISheet sheet = Iworkbook.GetSheetAt(0);

        IRow headerRow = sheet.GetRow(0);
        int cellCount = headerRow.LastCellNum;  //LastCellNum = PhysicalNumberOfCells
        int rowCount = sheet.LastRowNum;        //LastRowNum = PhysicalNumberOfRows - 1

        SessionKehuExcel = new DataTable();

        JsonReader reader = store_listdata.Reader[0] as JsonReader;
        foreach (RecordField recordField in reader.Fields)
        {
            SessionKehuExcel.Columns.Add(recordField.Name);
        }

        for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
        {
            IRow row = sheet.GetRow(i);
            DataRow dataRow = SessionKehuExcel.NewRow();

            if (row != null)
            {
                dataRow["RowNO"] = i;
                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                    {
                        string val = GetCellValue(row.GetCell(j));
                        if (j == 6) //处理进口国产
                            dataRow[j + 1] = val == "进口" ? 1 : 0;
                        else
                            dataRow[j + 1] = val;
                    }
                }
            }
            SessionKehuExcel.Rows.Add(dataRow);
        }
        store_listdata.DataSource = SessionKehuExcel;
        store_listdata.DataBind();
    }
    


    /// <summary>
    /// 根据Excel列类型获取列的值
    /// </summary>
    /// <param name="cell">Excel列</param>
    /// <returns></returns>
    private static string GetCellValue(ICell cell)
    {
        if (cell == null)
            return string.Empty;
        switch (cell.CellType)
        {
            case CellType.Blank:
                return string.Empty;
            case CellType.Boolean:
                return cell.BooleanCellValue.ToString();
            case CellType.Error:
                return cell.ErrorCellValue.ToString();
            case CellType.Numeric:
            case CellType.Unknown:
            default:
                return cell.ToString();
            case CellType.String:
                return cell.StringCellValue;
            case CellType.Formula:
                try
                {
                    HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
                    e.EvaluateInCell(cell);
                    return cell.ToString();
                }
                catch
                {
                    return cell.NumericCellValue.ToString();
                }
        }
    }


    [DirectMethod]
    public void SaveData()
    {
        if (SessionKehuExcel == null || SessionKehuExcel.Rows.Count == 0)
        {
            X.Msg.Alert("系统提示", "未上传Excel!或excel内容不符合").Show();
            return;
        }
        bool returnTrans = true;
        CommonLib.BLL.MyTransaction myTrans = new CommonLib.BLL.MyTransaction();
        try
        {
            myTrans.BeginTransaction();

            int index = 2;
            foreach (DataRow row in SessionKehuExcel.Rows)
            {
                bool newModel = false;
                string modelsql = string.Format(" CargoName='{0}' and Spec='{1}' and Version='{2}' and ProduceName='{3}' and Area='{4}' ", row["CargoName"], row["Spec"], row["Version"], row["ProduceName"], row["Area"]);

                CRMMis.Model.ERP_CusCargo model = CRMMis.BLL.ERP_CusCargo.GetModel(myTrans, modelsql);
                if (model == null)
                {
                    model = new CRMMis.Model.ERP_CusCargo();
                    newModel = true;
                    model.InUser = BS_LoginUserID;
                    model.InDate = DateTime.Now.ToDateTimeString();
                    model.State = 1;

                }
                model.CargoName = row["CargoName"].ToString().Trim();
                PinWbCode pin = new PinWbCode();
                model.Spell = pin.GetPinyin(model.CargoName);
                model.Spec = row["Spec"].ToString().Trim();
                model.Version = row["Version"].ToString().Trim();
                model.ProduceName = row["ProduceName"].ToString().Trim();
                model.Area = row["Area"].ToString().Trim();
                model.Unit = row["Unit"].ToString().Trim();
                model.IsImported = Convert.ToInt32(row["IsImported"].ToString());


                if (newModel)
                {
                    model.CargoId = CRMMis.BLL.ERP_CusCargo.GetNewID();
                    CRMMis.BLL.ERP_CusCargo.Add(myTrans, model);
                }
                else
                {
                    CRMMis.BLL.ERP_CusCargo.Update(myTrans, model);
                }
                //价格更新
                decimal Supplyprice = Convert.ToDecimal(row["Supplyprice"]);
                var dModel = CRMMis.BLL.ERP_CusCargo_Detail.GetModel(myTrans, "CargoId=" + model.CargoId + " and Supplyprice=" + Supplyprice + " and state=1");
                if (dModel == null)
                {
                    //禁用其他的价格
                    CRMMis.BLL.ERP_CusCargo_Detail.DisableCusCargo_DetailByCargoId(myTrans, model.CargoId);

                    //新增价格
                    dModel = new CRMMis.Model.ERP_CusCargo_Detail();
                    dModel.DetailId = CRMMis.BLL.ERP_CusCargo_Detail.GetNewID();
                    dModel.CargoId = model.CargoId;
                    dModel.InUser = BS_LoginUserID;
                    dModel.InDate = DateTime.Now.ToDateTimeString();
                    dModel.State = 1;
                    dModel.Supplyprice = Supplyprice;
                    dModel.Sos_No = row["Sos_No"].ToString();
                    CRMMis.BLL.ERP_CusCargo_Detail.Add(myTrans, dModel);
                }
                index++;
                
            }

            myTrans.CommitTran();
            returnTrans = false;
            X.Msg.Alert("系统提示", "保存成功！").Show();
            SessionKehuExcel = null;
            store_listdata.DataSource = new string[0];
            store_listdata.DataBind();
        }
        catch (Exception ex)
        {
            myTrans.RollBackTran();
            X.Msg.Alert("系统提示", "保存失败！</br>" + ex.Message).Show();
        }
        finally
        {
            if (returnTrans)
                myTrans.RollBackTran();
        }
    }

}